Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Building updates into an application

In this section, you build a new window to display data and capture updates, along with a separate procedure to validate those updates and write them to the database. Because you should get into the habit of not mixing user interface procedures and data management procedures, the UI and the database access are in separate procedures. You’ll use temp-tables to pass data back and forth and display data from those temp-tables in the user interface.

To define temporary tables for your window:

In the AppBuilder, select New Window.
Click the Procedure settings button:



First, you need to define three temp-tables that hold data received from the database. If you let the AppBuilder generate the definitions for them, you can then provide a user interface for them just as you can for database tables.

In the Procedure Settings dialog box, click the Temp-Table Definitions button .
Click the Add button, then select the Customer table from the Table Selector dialog box:



By default this generates a temp-table definition for a table with the same name, Customer, that is exactly LIKE the database table.

To change this default, type ttCust as the Table Name:



Note that there is a NO-UNDO toggle box that is checked on by default. Leave this on. This option adds the NO-UNDO keyword to the temp-table definitions, which is appropriate because Progress does not need to roll back any changes to the temp-tables themselves as part of a transaction.

Click Add again, then this time select the Order table.
In the editor labeled Additional Fields, add a new field definition for a CHARACTER field called TransType. Your procedure uses this to keep track of changed Order records.
In the same editor, define an index for your temp-table called OrderIdx with the OrderNum and TransType fields:



Repeat Step 3 through Step 8 for the OrderLine table. Add a temp-table for OrderLine called ttOline with a TransType field and an INDEX OlineIdx on OrderNum, LineNum, and TransType.
Click OK to save all these new temp-table definitions.

To set up the user interface of your window:

In the AppBuilder Palette, click the DB-Fields icon and then click on your design window.

When you use the AppBuilder to define temp-tables for a procedure, it keeps track of them by treating them as if they were in a special database called Temp-Tables, so you see this listed along with the actual database you’re connected to:



Select Temp-Tables from the Databases list and ttOrder from the Tables list.
From the Multi-Field Selector dialog box, choose the fields: OrderNum, OrderDate, PromiseDate, ShipDate, PO, and OrderStatus, and lay them out in the frame.
Give your window the title Order Updates.
Give the window’s frame the name OrderFrame.
Save this procedure as h-OrderUpdate.w:



Note that because you used the LIKE keyword to define your ttOrder temp-table based on the Order database table, its fields inherit all the attributes of the corresponding database fields, including their label, data type, and display type.

Add a rectangle under the Order fields and give it a Background Color of brown just to create a divider between the Order fields and the rest of the frame.
Add DB-Fields from the temp-table ttCust.

When you go to do this, the AppBuilder tries to define a default join between ttCust and the ttOrder temp-table you’ve already used in the frame. It’s unable to do this for temp-tables so it puts up an Advisor message to this effect.

Select the Cancel option to tell the AppBuilder not to worry about the default join, then click OK:



Your procedure will receive the correct Customer for an Order from the database procedure it uses.

Arrange the fields from ttCust in the lower part of the frame.
Disable the ttCust fields. These are used only to display values from the Order’s Customer.

When you’re done, your design window should look something like this:

To add a browse for the Order’s OrderLines:

Select the Browse icon from the AppBuilder Palette and drop it onto the bottom of the design window.
Add ttOline from the list of Available Tables.

Once again the AppBuilder tries to provide you with a default join that you won’t need.

Select ttOline from the Selected Tables list and click the Switch Join Partners button.
In the Select Related Table dialog box, select (None):



Click OK. The Query Builder shows ttOline without a join to another table:



Click the Fields button in the Query Builder. Select all of the ttOline fields in an order such as: OrderNum, Line Num, Item Num, Price, Qty, Extended Price, and Discount.

Note that the fields from the ttCust and ttOrder tables are also in the list, but you don’t want them in the browse.

Check on the Enable toggle box for the columns ItemNum, Price, Qty, Discount, and OrderLineStatus so that the user can change these values for an Order’s lines.
Change the browse Object Name to OlineBrowse.

Now the window should look like this:

To write code that populates the fields and the browse and lets you update the Order:

In the Definitions section, define a handle called hLogic:

/* Local Variable Definitions ---                                    */ DEFINE VARIABLE hLogic AS HANDLE      NO-UNDO.

This variable holds the procedure handle of the procedure you write next where all the logic is to read records for the Order from the database and accept changes back from the client.

In the main block, add a line to run the h-OrderLogic.p logic procedure as a persistent procedure and save its handle:

MAIN-BLOCK: DO ON ERROR   UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK    ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:   RUN enable_UI.   RUN h-OrderLogic.p PERSISTENT SET hLogic.   IF NOT THIS-PROCEDURE:PERSISTENT THEN     WAIT-FOR CLOSE OF THIS-PROCEDURE. END.

Add two buttons to the right of the OrderNum field: call one btnFetch with the label Fetch, and call the other btnSave with the label Save.

You’ll use these buttons to retrieve data from the logic procedure and then to return any changes. The user can enter an Order number and then clicks Fetch to retrieve it with its Customer and OrderLines, make changes, and then send the changes back to the logic procedure by clicking Save.

In the CHOOSE trigger for btnFetch, define a buffer named bUpdateOline for the ttOline table:

DO:   DEFINE BUFFER bUpdateOline FOR ttOline.

You’ll use this buffer to have two records for each OrderLine: one with any changes that are made and one that saves the original values before changes.

Empty the three temp-tables to prepare for fetching a requested Order and its customer and lines:

/* Remove any data leftover from previous calls. */     EMPTY TEMP-TABLE ttCust.     EMPTY TEMP-TABLE ttOrder.     EMPTY TEMP-TABLE ttOline.

Run an internal procedure called fetchOrder in the logic procedure’s handle. This needs to pass in the value in the OrderNum field. It gets back three temp-tables for the Order and its related data:

/* Retrieve all related records for the Order. */     RUN fetchOrder IN hLogic       (INPUT INTEGER(ttOrder.OrderNum:SCREEN-VALUE),        OUTPUT TABLE  ttOrder,        OUTPUT TABLE  ttCust,        OUTPUT TABLE  ttOline) .

Add code so that if the Order isn’t found or if there’s some other error, this comes back in the RETURN-VALUE:

/* Check for an error such as order-not-found. */     IF RETURN-VALUE NE "" THEN     DO:       MESSAGE RETURN-VALUE.       RETURN NO-APPLY.     END.

Because there’s always exactly one ttOrder record (the one the user requested) and one ttCust record for that Order, you can just FIND these in the temp-tables that came back to bring them into their respective buffers, and then display their field values:

FIND ttOrder.     FIND ttCust.     DISPLAY ttOrder.OrderNum ttOrder.OrderDate ttOrder.PromiseDate           ttOrder.ShipDate ttOrder.PO ttOrder.OrderStatus           ttCust.CustNum ttCust.NAME ttCust.City ttCust.State           ttCust.CreditLimit ttCust.Balance       WITH FRAME OrderFrame.

For each of the ttOlines, you need to create a copy of the record that holds any updates that are made to it. This is so that you can also keep the original before image of each record to compare with the database, to see if the record has been changed by another user. The copy that can be updated is marked with a TransType of “U”:

/* For each OrderLine, create a record to hold any updates. */     FOR EACH ttOline WHERE ttOline.TransType = "":         CREATE bUpdateOline.         BUFFER-COPY ttOline TO bUpdateOline ASSIGN TransType = "U".     END.

To display the OrderLine records the user can update, you just open the browse’s query:

/* Display just the updatable records in the browse. */    OPEN QUERY OlineBrowse FOR EACH ttOline WHERE ttOline.TransType = "U". END.

To create the separate logic procedure that retrieves data from the database and later applies any updates to the database:

Select New Structured Procedure in the AppBuilder to create a procedure you’ll call h-OrderLogic.p.
In the Definitions section, repeat the temp-table definitions. Make the temp-tables NO-UNDO (which is how you defined them in the calling procedure):

/* *************************** Definitions ************************ */ DEFINE TEMP-TABLE ttCust NO-UNDO LIKE Customer. DEFINE TEMP-TABLE ttOrder NO-UNDO LIKE Order FIELD TransType AS CHARACTER     INDEX OrderIdx OrderNum TransType. DEFINE TEMP-TABLE ttOline NO-UNDO LIKE OrderLine FIELD TransType AS CHARACTER     INDEX OlineIdx OrderNum LineNum TransType.

You define them NO-UNDO because changes to the records in the temp-tables themselves do not need to be rolled back by the Progress transaction mechanism. Defining them as NO-UNDO saves Progress the overhead of preparing to roll back changes.

In a larger application, these could become include files used in all the procedures that reference these tables.

Add the fetchOrder internal procedure to load all the needed data for the OrderNum passed in:

/*---------------------------------------------------------------------   Procedure fetchOrder:   Purpose:  Return an Order, its Customer, and all its related OrderLines             to the caller. ---------------------------------------------------------------------*/ DEFINE INPUT PARAMETER piOrderNum AS INTEGER    NO-UNDO. DEFINE OUTPUT PARAMETER TABLE FOR ttOrder. DEFINE OUTPUT PARAMETER TABLE FOR ttCust. DEFINE OUTPUT PARAMETER TABLE FOR ttOline. FIND Order WHERE Order.OrderNum = piOrderNum NO-LOCK  NO-ERROR. IF NOT AVAILABLE(Order) THEN     RETURN "Order not found". EMPTY TEMP-TABLE ttOrder. CREATE ttOrder. BUFFER-COPY Order TO ttOrder. FIND Customer OF Order. EMPTY TEMP-TABLE ttCust. CREATE ttCust. BUFFER-COPY Customer TO ttCust. EMPTY TEMP-TABLE ttOline. FOR EACH OrderLine OF Order:     CREATE ttOline.     BUFFER-COPY OrderLine TO ttOline. END. RETURN "". END PROCEDURE.

Now the retrieval end of your sample procedure window should work.

To test it, run h-OrderUpdate.w, type an Order Number, and then click the Fetch button:



You can modify fields in the Order record and in one or more of the browse rows for OrderLines. Remember that you are not making changes to the database when you do this, because your user interface is just working with temp-tables. So you need to write trigger code for the Save button and a procedure in the logic procedure to handle the updates.

To add the code that handles the database updates:

Add this trigger code for the Save button btnSave. It defines a second buffer for each of the updateable temp-tables, and a variable to hold the result of a buffer compare:

DO:   DEFINE BUFFER bOldOrder FOR ttOrder.   DEFINE BUFFER bOldOline FOR ttOline.   DEFINE VARIABLE cCompare AS CHARACTER  NO-UNDO.

The procedure hasn’t saved changes for the Order into the temp-table record from the screen buffer yet.

Create a temp-table record to hold the updates and then assign all the screen fields, saving the original version in the separate buffer bOldOrder:

/* Create an Update record in the Order temp-table for any changes. */   FIND bOldOrder.   CREATE ttOrder.   BUFFER-COPY bOldOrder TO ttOrder.   ASSIGN ttOrder.PromiseDate ttOrder.ShipDate ttOrder.PO          ttOrder.OrderStatus ttOrder.TransType = "U".

Include the following code to check whether any fields were actually changed by comparing the two records. If there are no changes, it deletes the before image as a signal to the SAVE procedure:

/* Check to see if anything was changed, and if not, then delete the     before image record. */   BUFFER-COMPARE ttOrder EXCEPT TransType TO bOldOrder SAVE cCompare.   IF cCompare = "" THEN      DELETE bOldOrder.

The code on the Fetch button creates an update record for every OrderLine.

Add code to check which records were actually updated and delete the before image for those records that weren’t. This code tells the save procedure which records changed and allows the window to browse all the OrderLines by selecting those marked with a “U”:

/* For every OrderLine, compare the original and Update records to see      if anything was in fact changed; if not, delete the original before      saving. */   FOR EACH bOldOline WHERE bOldOline.TransType = "U":       FIND ttOline WHERE ttOline.OrderNum = bOldOline.OrderNum AND          ttOline.LineNum = bOldOline.LineNum AND ttOline.TransType = "".       BUFFER-COMPARE bOldOline EXCEPT TransType TO ttOline SAVE cCompare.       IF cCompare = "" THEN  /* If there were no changes, */          DELETE ttOline. /* delete the unchanged version of the rec. */   END.

Run a saveOrder procedure to return the changes. The Order and OrderLine tables are passed as INPUT-OUTPUT parameters to allow the logic procedure to return either changes made by another user, if the update is rejected for that reason, or the final versions of all the records, in case they are further changed by update logic:

RUN saveOrder IN hLogic       (INPUT-OUTPUT TABLE ttOrder,       INPUT-OUTPUT TABLE ttOLine).

Add code so that the RETURN-VALUE indicates the Order was changed out from under you. The new values are displayed:

IF RETURN-VALUE = "Changed Order" THEN   DO:       MESSAGE "The Order has been changed by another user.".       FIND ttOrder WHERE ttOrder.TransType = "U".       DISPLAY ttOrder.OrderDate ttOrder.PromiseDate ttOrder.Shipdate               ttOrder.PO ttOrder.OrderStatus WITH FRAME OrderFrame.       RETURN NO-APPLY.   END.

Add code that, if any OrderLines changed, returns and displays the updates made by another user. Otherwise, it reopens the browse query to display the final versions of all the OrderLines, including any changes made in the logic procedure. Those changes are all in the temp-table record versions marked with a “U”:

ELSE IF RETURN-VALUE = "Changed Oline" THEN      MESSAGE "One or more OrderLines have been changed by another user.".    OPEN QUERY OlineBrowse FOR EACH ttOline WHERE ttOline.TransType = "U". END.  /* END trigger block */

To create another internal procedure called saveOrder in the logic procedure h-OrderLogic.p:

In the save procedure, define the INPUT-OUTPUT parameters for the two updated tables:

/*---------------------------------------------------------------------   Procedure saveOrder:   Purpose:     Accepts updates to an Order and its related records and                saves them to the database, returning any field values                calculated during the update process. ---------------------------------------------------------------------*/ DEFINE INPUT-OUTPUT PARAMETER TABLE FOR ttOrder. DEFINE INPUT-OUTPUT PARAMETER TABLE FOR ttOline.

Define a second buffer for each of them to allow original values to be compared with the database:

DEFINE BUFFER bUpdateOrder FOR ttOrder. DEFINE BUFFER bUpdateOline FOR ttOline.

Define explicit buffers for the database tables to make sure that no record is inadvertently scoped to a higher level in the procedure (which is always a good idea in writing code that does updates):

DEFINE BUFFER Order FOR Order. DEFINE BUFFER OrderLine FOR OrderLine.

Add a character variable that holds a record of any differences found by a buffer compare:

DEFINE VARIABLE cCompare AS CHARACTER NO-UNDO.

Open a transaction block so that all the comparisons with the original database records and all the update are made together. Then try to find the before image of the Order temp-table record. If it’s there, then the Order was changed. Next, find the corresponding database record using the table’s unique primary key. You do this with an EXCLUSIVE-LOCK because you’ll later update this database record if no one else has changed it. This also assures that no one else can change it after you first read it. You compare the two and reject the update if the record has been changed by someone else:

DO TRANSACTION ON ERROR UNDO, LEAVE:     FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.     IF AVAILABLE (ttOrder) THEN       /* If this rec is there then the Order was updated on the client. */     DO:       FIND Order WHERE Order.OrderNum = ttOrder.OrderNum EXCLUSIVE-LOCK.       BUFFER-COMPARE ttOrder TO Order SAVE cCompare.       IF cCompare NE "" THEN       DO:     /* Somebody else has changed the record since we read it. */             BUFFER-COPY Order TO ttOrder.   /* Return the changes. */             RETURN "Changed Order".          END. /* END DO IF cCompare Not "" */

If it hasn’t been changed, then you find the Update version of the ttOrder and copy your changes from there to the database record:

ELSE DO: /* FIND the updated tt rec and save the changes. */             FIND bUpdateOrder WHERE bUpdateOrder.TransType = "U".             BUFFER-COPY bUpdateOrder TO Order.  /* Save our changes. */         END.   /* END ELSE DO */       END.     /* END OF AVAILABLE ttOrder */

Do the same for any changed OrderLines. For each before-image record, which you read into the ttOline buffer, find the changed version in the bUpdateOline buffer, find the corresponding database record, compare the before version with the database, and reject the update if someone else has changed it:

/* For each OrderLine that has a before-image (unchanged) record,       make sure it hasn't been changed by another user. */     FOR EACH ttOline WHERE ttOline.TransType = "":         /* Bring the updated version into the other buffer. */         FIND bUpdateOline WHERE bUpdateOline.TransType = "U" AND                 bUpdateOline.OrderNum = ttOline.OrderNum AND                 bUpdateOline.LineNum = ttOline.LineNum.         FIND OrderLine WHERE OrderLine.OrderNum = ttOline.OrderNum AND                 OrderLine.LineNum = ttOline.LineNum EXCLUSIVE-LOCK.         BUFFER-COMPARE ttOline TO OrderLine SAVE cCompare.         IF cCompare NE "" THEN         DO: /* Somebody else has changed the record since we read it.       Copy the changes to the Update version to display on the client. */         BUFFER-COPY OrderLine TO bUpdateOline. /* Return the changes. */             RETURN "Changed Oline".         END.  /* END DO IF cCompare NE "" */

Otherwise, apply your changes to the database:

ELSE DO:  /* Save our OrderLine changes. */            BUFFER-COPY bUpdateOline TO OrderLine.

There’s some additional code here that needs explanation. You release the OrderLine, which forces it to be written immediately to the database without waiting for the iteration of the FOR EACH ttOline block within the transaction. As the record is written out, any database trigger procedures for the table execute. Trigger procedures let you execute standard update logic when a database record is modified, created, or deleted, so that it is always run no matter where the update occurs within your application. You learn about how to write and use trigger procedures in the "Defining database triggers" section. The trigger is a kind of side effect to the update.

To see its effects, you need to re-read the record after the RELEASE forces the trigger to fire and then bring any changes the trigger made back into the temp-table, where it can be returned to the client and displayed:

RELEASE OrderLine.      /* Re-find the db record to capture any changes made by a trigger. */      FIND OrderLine WHERE OrderLine.OrderNum = bUpdateOline.OrderNum AND                OrderLine.LineNum = bUpdateOline.LineNum NO-LOCK.      BUFFER-COPY OrderLine TO bUpdateOline.      END.    /* END ELSE DO If we updated the OrderLine */    END.      /* END DO FOR EACH ttOline */   END.        /* END DO Transaction */ END PROCEDURE.

To test your logic procedure:

Save h-OrderLogic.p.
Rerun the OrderUpdate window.
Select Order 124 again (or any other Order you like), then click Fetch.
Make a change to one or more of the Order fields. For example, enter a purchase order number (PO).
Change the Price or Qty for some of the OrderLines:



Click the Save button.

Notice that the Extended Price field (which is not enabled for input) changes for any OrderLine whose Price or Qty you changed:



This is the effect of the database trigger procedure for the WRITE event on the OrderLine table. Re-reading the changed record into the temp-table, sending it back to the client window, and reopening the browse query displayed those changes. Later, you’ll learn how to write these trigger procedures yourself.